package cn.net.lwj.singledb.service.impl;

import cn.net.lwj.common.service.Impl.BaseServiceImpl;
import cn.net.lwj.common.util.DBTool;
import cn.net.lwj.singledb.service.JdbcService;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

/**
 * 测试事务管理
 */
@Service("jdbcService")
@SuppressWarnings({"javadoc"})
@Slf4j
public class JdbcServiceImpl extends BaseServiceImpl implements JdbcService {

    /**
     * 测试单事务
     * 手动插入异常测试
     * @param name
     * @param addr
     */
    public void saveUser(String name, String addr, boolean isException, boolean isThrow) {
        DBTool dbTool = new DBTool();
        List<Map<String, Object>> list = new ArrayList<>();
        String sql = "INSERT INTO `test`.`t_user`( `name`, `addr`,`sysTime`) VALUES (?,?,?);\n";
        String querySql = " SELECT * FROM t_user";
        Connection conn = null;
        try {
            conn = dbTool.getConnection();
            DBTool.beginTransaction(conn);
            list = DBTool.queryForList(conn, querySql);
            log.info("[{}]插入前:", list);
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            ps.setString(2, addr);
            //ps.setString(3, formatdate.format(new Date()));
            ps.setDate(3,new java.sql.Date(System.currentTimeMillis()));
            ps.execute();
            //测试在插入异常前手动提交一次,那么已经提交的不会回滚
            //DBTool.commitTransaction(conn);
            sql = "UPDATE t_user SET sysTime=NOW() WHERE id=2";
            DBTool.execute(conn, sql);

            list = DBTool.queryForList(conn, querySql);
            log.info("[{}]插入后:", list);
            //插入异常后回滚正常
            if (isException) {
                "".substring(5);
            }
            log.info("[{}]插入异常后:", list);
            DBTool.commitTransaction(conn);
        } catch (Exception e) {
            e.printStackTrace();
            DBTool.rollBackTransaction(conn);
            if (isThrow) {
                throw new RuntimeException(e.getMessage());
            }
        }
    }

    /**
     * 测试字方法中嵌套方法1  字方法正常
     * 执行结果:字方法插入更新成功,当前方法执行失败(回滚)
     */
    public void testTranaction() {
        DBTool dbTool = new DBTool();
        List<Map<String, Object>> list = new ArrayList<>();
        String sql = "";
        Connection conn = null;
        try {
            conn = dbTool.getConnection();
            DBTool.beginTransaction(conn);
            //sql = "DELETE FROM t_user WHERE name='罗文军1'";
            //DBTool.execute(conn, sql);
            //如果前面有删除方法,会异常,类似死锁,在后面则不会
            //嵌套有单独事务的方法,本身自己也有手动事务
            saveUser("罗文军2", "綦江", false, true);
            sql = "DELETE FROM t_user WHERE name='罗文军2'";
            DBTool.execute(conn, sql);
            sql = "UPDATE t_user SET sysTime=NOW() WHERE name='罗文军'";
            //1.手动加入异常,子方法正常 执行结果:字方法插入更新成功,当前方法执行失败(回滚)
            "".substring(5);
            DBTool.execute(conn, sql);
            DBTool.commitTransaction(conn);
        } catch (Exception e) {
            e.printStackTrace();
            DBTool.rollBackTransaction(conn);
        }
    }

    /**
     * 测试字方法中嵌套方法2  字方法异常
     * 如果字方法抛出异常:主方法也直接中断
     * 如果字方法不抛出异常: 主方法按照原有规则进行
     */
    public void testTranaction1() {
        DBTool dbTool = new DBTool();
        List<Map<String, Object>> list = new ArrayList<>();
        String sql = "";
        Connection conn = null;
        try {
            conn = dbTool.getConnection();
            DBTool.beginTransaction(conn);
            //sql = "DELETE FROM t_user WHERE name='罗文军1'";
            //DBTool.execute(conn, sql);
            //如果前面有删除方法,会异常,类似死锁,在后面则不会
            //嵌套有单独事务的方法,本身自己也有手动事务
            saveUser("罗文军2", "綦江", true, false);
            sql = "DELETE FROM t_user WHERE name='罗文军2'";
            //DBTool.execute(conn, sql);
            sql = "UPDATE t_user SET sysTime=NOW() WHERE name='罗文军'";
            DBTool.execute(conn, sql);
            //1.手动加入异常,子方法正常 执行结果:字方法插入更新成功,当前方法执行失败(回滚)
            //"".substring(5);
            DBTool.execute(conn, sql);
            DBTool.commitTransaction(conn);
        } catch (Exception e) {
            e.printStackTrace();
            DBTool.rollBackTransaction(conn);
        }
    }

    public static void main(String[] args) {
        JdbcServiceImpl demo = new JdbcServiceImpl();
        //demo.saveUser("罗文军1", "綦江", true);
        //demo.testTranaction();
        demo.testTranaction1();

    }


    @Override
    public void test() {

    }
}
